USE [SI80ADRP]
GO

/****** Object:  View [dbo].[v_ReservationTime]    Script Date: 12/17/2014 18:21:01 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[v_ReservationTime]
AS
SELECT     s.UNITID, s.Dept AS DEPTID, s.SMANID, s.NAME AS SNAME, gs.GSALID, gs.WRKORDNO AS WORK_ORDER_ID, gs.VEHIID, gs.CUSTNO, gs.WTRID, gs.CONFNAME, 
                      gs.CONLNAME, gs.UWTEL AS CONTACT_TEL, gs.UEMAIL AS CONTACT_EMAIL, gt.STARTDT AS STARTTIME, gt.ENDDT AS ENDTIME, gt.DURATION, 
                      v.LICNO AS LIC_NO, v.SERIALNO, v.MAKE, v.MODEL, v.SUBMODEL, c.LNAME, c.FNAME, c.email, c.WTEL, gs.STYPE AS TRANSACTION_TYPE, 
                      c2.C2 AS TRANSACTION_NOTE, gs.ESTDELD AS PICKUP_DATE, gs.ESTDELTIME AS PICKUP_TIME, gr.NOTE, gs.RECEIVER, gr.GROWID, gs.SERVD, gs.WENDD, 
                      st.ENDDT AS WENDDT, st.STAMID
FROM         dbo.SMAN AS s INNER JOIN
                      dbo.VEHI AS v INNER JOIN
                      dbo.ALL_GSAL AS gs ON v.VEHIID = gs.VEHIID INNER JOIN
                      dbo.CUST AS c ON gs.CUSTNO = c.CUSTNO INNER JOIN
                      dbo.ALL_GTRES AS gt INNER JOIN
                      dbo.ALL_GTRS AS g ON gt.WTRID = g.WTRID ON gs.WTRID = g.WTRID AND gs._UNITID = gt._UNITID AND gs._UNITID = g._UNITID ON s.SMANID = gt.RESID AND 
                      s.UNITID = gs._UNITID INNER JOIN
                      dbo.ALL_GROW AS gr ON gs.GSALID = gr.GSALID AND gs._UNITID = gr._UNITID INNER JOIN
                      dbo.ALL_CORW AS c2 ON gs.STYPE = c2.C1 AND gs._UNITID = c2._UNITID LEFT OUTER JOIN
                      dbo.ALL_GRST AS grst ON gr.GSALID = grst.GSALID AND gr.GROWID = grst.GROWID AND gr._UNITID = grst._UNITID LEFT OUTER JOIN
                      dbo.ALL_STAM AS st ON st.STAMID = grst.STAMID AND gr._UNITID = st._UNITID
WHERE     (c2.CODAID = 'LIIKETAPTY') AND (c2.C7 LIKE '%K%')